#!/usr/bin/env python3
# -*- coding: utf-8 -*-

'''
Created on Tue Nov 13 14:09:42 2018

@author: elsa

获取当天数据 从 2018-11-14号开始 获取， 之前数据在数据表中已经有，因为我不可能每天都有空来获取一次，故而是删除表数据后，从 2018-11-14号开始
获取到操作当天，这样，数据量也不大。更新时间：交易日每日15点～17点之间

15亿<PB3||20亿<PB1.5 北京 深圳 浙江
 //毛利率高,科技含量高,市值低于15亿（可以2.5，3 PB 以下，最好11，12亿医药类）,市值低于20亿的（必须在PB1.5以下,比如同花顺，迪安诊断)
 NSArray* haveStr = @[@"北京",@"深圳",@"上海",@"广东", @"江苏",@"浙江",@"山东"];
 NSArray* noStr = @[@"高速公路",@"公用事业",@"烟草",@"农林牧渔",@"钢铁",@"煤",@"ST",@"天然气",@"油",@"建筑"];
 接近历吏最低PB %5
'''

import pymysql
import tushare as ts
import time
import math
import datetime
import pandas as pd
import socket
import urllib.error


# 打开数据库连接，获取 stock_list
db = pymysql.connect(host='localhost', 
                        user='root', 
                        passwd='taotao007', 
                        db='stock',
                        use_unicode=True,
                        charset='utf8')

cur = db.cursor()
sql = "select stock_basic_ts_code from stock_list"
try:
    cur.execute(sql)
    coderesults = cur.fetchall()	#获取查询的所有记录
    
    sql_delete ="delete from stock_today_price"  #删除所有stock_today_price
    cur.execute(sql_delete) #像sql语句传递参数

except Exception as e:
	raise e
finally:
	db.close()	#关闭连接



ts.set_token('b81d01e83e670dc4c0313032df361210a3b1141df4709c3aabe25b88')
#初始化pro接口
pro = ts.pro_api()

# 打开数据库连接，获取 stock_list
db = pymysql.connect(host='localhost', 
                        user='root', 
                        passwd='taotao007', 
                        db='stock',
                        use_unicode=True,
                        charset='utf8')

cur = db.cursor()


nowtime = datetime.datetime.now()
today   = "%s%s%s" % (nowtime.year,nowtime.month,nowtime.day)

#遍历结果
for row in coderesults :
    ts_code_every = row[0]

    #查询当前股票的所有历吏记录，主要是历事PB
    try:
        stockdaily = pro.daily_basic(ts_code=ts_code_every,start_date='20181114',end_date=today)
    except urllib.URLError as e:
        print(type(e))    #not catch
    except socket.timeout as e:
        print(type(e))    #catched
        
    print ('get daily_basic %s %s' % (ts_code_every, time.asctime( time.localtime(time.time()) ) ))

    for index,row in stockdaily.iterrows():
        
        #处理特殊字符
        data_close = row['close']
        data_turnover_rate = row['turnover_rate']
        data_turnover_rate_f =row['turnover_rate_f']
        data_pe =row['pe']
        data_pe_ttm =row['pe_ttm']
        data_pb =row['pb']
        data_ps =row['ps']
        data_ps_ttm =row['ps_ttm']
        data_total_share =row['total_share']
        data_float_share =row['float_share']
        data_free_share =row['free_share']
        data_total_mv =row['total_mv']
        data_circ_mv =row['circ_mv']
        
        if data_close is None or math.isnan(data_close):
            data_close = 0
        if data_turnover_rate is None or math.isnan(data_turnover_rate):
            data_turnover_rate = 0
        if data_turnover_rate_f is None or math.isnan(data_turnover_rate_f):
            data_turnover_rate_f = 0
        if data_pe is None or math.isnan(data_pe):
            data_pe = 0
        if data_pe_ttm is None or math.isnan(data_pe_ttm):
            data_pe_ttm = 0
        if data_pb is None or math.isnan(data_pb):
            data_pb = 0
        if data_ps is None or math.isnan(data_ps):
            data_ps = 0
        if data_ps_ttm is None or math.isnan(data_ps_ttm):
            data_ps_ttm = 0
        if data_total_share is None or math.isnan(data_total_share):
            data_total_share = 0
        if data_float_share is None or math.isnan(data_float_share):
            data_float_share = 0
        if data_free_share is None or math.isnan(data_free_share):
            data_free_share = 0
        if data_total_mv is None or math.isnan(data_total_mv):
            data_total_mv = 0
        if data_circ_mv is None or math.isnan(data_circ_mv):
            data_circ_mv = 0
      
        sql_insert ="insert into stock_today_price(daily_basic_ts_code,daily_basic_trade_date,daily_basic_close,daily_basic_turnover_rate, \
        daily_basic_turnover_rate_f,daily_basic_pe,daily_basic_pe_ttm,daily_basic_pb,daily_basic_ps, \
        daily_basic_ps_ttm,daily_basic_total_share,daily_basic_float_share,daily_basic_free_share,daily_basic_total_mv,\
        daily_basic_circ_mv) values('%s','%s',%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f);" % (row['ts_code'],row['trade_date'], \
        data_close,data_turnover_rate, data_turnover_rate_f,data_pe,data_pe_ttm,data_pb,data_ps, \
        data_ps_ttm,data_total_share,data_total_share,data_free_share,data_total_mv,data_circ_mv)
        #print (sql_insert)
        cur.execute(sql_insert)
       
    try:
         db.commit()
    except Exception as e:
        #回滚
	    raise e
  
db.close()

print ("get today data finished")

#---------------------------------处理最低PB-------------------------------------------

#1：stock_today_price 得到 当天 PB 小于等于 1 的 CODE
#2：这些行业不要 公路",@"公用",@"烟草",@"农林牧渔"，软件，水力，化工，水泥，@"钢铁",@"煤",@"ST",@"气",@"油",@"建筑 从 stock_list
#   只要的省或城市 @"北京",@"深圳",@"上海",@"广东", @"江苏",@"浙江",@"山东"] stock_list
#3：从 stock_history_pb 获取每个股票的 最低 PB 从 每个CODE里查找 ，计算 2者百比分 小于 5%


first_code = []
first_pb   = []

# 打开数据库连接，获取 stock_list
db = pymysql.connect(host='localhost', 
                        user='root', 
                        passwd='taotao007', 
                        db='stock',
                        use_unicode=True,
                        charset='utf8')

cur = db.cursor()
sql = "select daily_basic_ts_code,daily_basic_pb from stock_today_price where daily_basic_pb<=1.0 and daily_basic_pb>0 and daily_basic_trade_date=%s" % today
try:
    cur.execute(sql)
    todaypbresults = cur.fetchall()	#获取查询的当天所有记录
    
    #过滤城市与 行业
    for row in todaypbresults:
        ts_code = row[0]
        ts_pb   = row[1]
        
        sql = "select stock_basic_area,stock_basic_industry from stock_list where stock_basic_ts_code='%s'" % ts_code
        cur.execute(sql)
        coderesults = cur.fetchall()	#获取地区与行业
        
        for coderesult in coderesults:
            area = coderesult[0]
            industry = coderesult[1]
            area.replace(' ','')
            industry.replace(' ','')
            
            #城市包含关系 #行业过滤关系
            if area in "北京深圳上海广东江苏浙江山东杭州" and industry not in "公路公用事业烟草农林牧渔软件水泥化工钢铁煤ST气油建筑" :
            #if industry not in "公路公用烟草农林牧渔软件水泥化工钢铁煤ST气油建筑" :
               first_code.append(ts_code)  #加入列表
               first_pb.append (ts_pb)
               
    
    first_table=pd.DataFrame({'code':first_code,'pb':first_pb})

    print ("first_table: %s" % first_table)
    
    second_code = []
    second_pb   = []
    second_minpb = []
    
    for index,row in first_table.iterrows():
        ts_code  = row['code']
        today_pb = row['pb']
        #获取此代码最低PB FROM stock_history_pb 还有 stock_today_price 2018114后的PB，要参与计算最小PB
        sql = "select daily_basic_ts_code,min(daily_basic_pb) as min_pb from `stock_history_pb` where daily_basic_pb>0 \
        and daily_basic_ts_code='%s' group by daily_basic_ts_code" % ts_code
        cur.execute(sql)
        coderesults = cur.fetchall()	#获取最低PB from stock_history_pb
        
        stock_history_min_pb = 0
        stock_today_price_min_pb =0  #不要当天
        minpb = 0
        
        for coderesult in coderesults:
            stock_history_min_pb = coderesult[1]
        
        sql = "select daily_basic_ts_code,min(daily_basic_pb) as min_pb from `stock_today_price` where daily_basic_pb>0 \
        and daily_basic_ts_code='%s' and daily_basic_trade_date!='%s' group by daily_basic_ts_code" % (ts_code,today)
        cur.execute(sql)
        coderesults = cur.fetchall()
        for coderesult in coderesults:
            stock_today_min_pb = coderesult[1]
         
        #历吏与现在最小值取的
        if stock_history_min_pb>stock_today_min_pb:
            minpb = stock_today_min_pb
        else:
            minpb = stock_history_min_pb
            
        if today_pb*1.0/minpb<=1.05 :
            second_code.append(ts_code)  #加入列表
            second_pb.append(today_pb)
            second_minpb.append (minpb)
            print("code：%s   pb：%s   minpb: %f" % (ts_code, today_pb, minpb))
                
    
except Exception as e:
	raise e
finally:
	db.close()	#关闭连接
    

    
    
    